import sqlite3

class SQLiteHelper:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def create_table(self, table_name, columns):
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns});"
        self.cursor.execute(query)
        self.conn.commit()
        print(f"Table '{table_name}' created successfully.")

    def drop_table(self, table_name):
        query = f"DROP TABLE IF EXISTS {table_name};"
        self.cursor.execute(query)
        self.conn.commit()
        print(f"Table '{table_name}' dropped successfully.")

    def insert_data(self, table_name, fields, values):
        placeholders = ', '.join(['?' for _ in range(len(values))])
        fieldholders = ', '.join(fields)
        query = f"INSERT INTO {table_name} ({fieldholders}) VALUES ({placeholders});"
        self.cursor.execute(query, values)
        self.conn.commit()
        print("Data inserted successfully.")
        return self.cursor.lastrowid

    def update_data(self, table_name, column, value, condition):
        query = f"UPDATE {table_name} SET {column}=? WHERE {condition};"
        self.cursor.execute(query, (value,))
        self.conn.commit()
        print("Data updated successfully.")

    def delete_data(self, table_name, condition):
        query = f"DELETE FROM {table_name} WHERE {condition};"
        self.cursor.execute(query)
        self.conn.commit()
        print("Data deleted successfully.")

    def select_data(self, table_name, columns=None, condition=None):
        column_str = '*' if columns is None else ', '.join(columns)
        query = f"SELECT {column_str} FROM {table_name}"
        if condition:
            query += f" WHERE {condition}"
        query += ";"

        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        return rows

    def __del__(self):
        self.cursor.close()
        self.conn.close()
        
# 示例用法
if __name__ == "__main__":
    # 创建数据库助手对象并连接数据库
    helper = SQLiteHelper("mydatabase.db")

    # 创建表
    helper.create_table("students", "id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER")

    # 插入数据
    data = (1, "Alice", 20)
    helper.insert_data("students", data)

    # 查询数据
    rows = helper.select_data("students")
    print(f"Data retrieved: {rows}")

    # 更新数据
    helper.update_data("students", "age", 21, "id=1")

    # 查询更新后的数据
    rows = helper.select_data("students")
    print(f"Updated data: {rows}")

    # 删除数据
    helper.delete_data("students", "id=1")

    # 删除表
    helper.drop_table("students")